﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Entity;
using TanWeb.Helpers;
using TanWeb.Models.Entities;
using TanWeb.Models.DataAccess.Infrastructure;
using TanWeb.Models.Entities.Master;
using System.Collections;

namespace TanWeb.Models.DataAccess
{
    public class ProductDataAccess : BaseDataAccess
    {
        //===============================================================
        //  コンストラクタ等宣言
        //===============================================================
        #region /***    Constructor Destructor Declared    ***/

        /// <summary>
        /// コンストラクタ
        /// </summary>
        /// <param name="dbContext">データベースコンテキスト</param>
        public ProductDataAccess(DbContext dbContext) : base(dbContext) { }

        #endregion

        public List<ProductProfile> GetProductInfo(string groupId, string subGroupId, int pageIndex = 1)
        {
            // ログを記述する
            logger.Info("\t\tStart DataAccess: Product; Function: GetProductInfo");

            List<ProductProfile> lstProductProfile = new List<ProductProfile>();
            string sql = string.Empty;
            string filter = string.Empty;

            try
            {
                filter += groupId != string.Empty ? " AND p.GroupId = {0}" : string.Empty;
                filter += subGroupId != string.Empty ? " AND p.SubGroupId = {1}" : string.Empty;
                filter = string.Format(filter, groupId, subGroupId);

                sql = string.Format(
                            @"SELECT id, GroupId, SubGroupId, 
                                Name, Price, Description, ImageName
                            FROM (
                                SELECT ROW_NUMBER() OVER (ORDER BY p.UpdateDate desc, p.id desc) AS Row,
                                    p.id, p.GroupId, p.SubGroupId, p.Name, 
                                    p.Price, p.Description, i.ImageName
                                FROM Product p
                                INNER JOIN Image i 
                                    ON p.id = i.ProductId
                                    AND p.GroupId = i.GroupId
                                    AND p.SubGroupId = i.SubGroupId
                                    AND i.id = (
                                        SELECT Min(id)
                                        FROM Image
                                        WHERE GroupId = i.GroupId
                                            AND SubGroupId = i.SubGroupId
                                            AND ProductId = p.id
                                    )
                                WHERE p.del_flg = 0 {0}
                            ) temp
                            WHERE ROW >= {1} AND ROW <= {2}", filter,
                            ((pageIndex - 1) * Constants.MAX_PAGE_RECORD + 1).ToString(), (pageIndex * Constants.MAX_PAGE_RECORD).ToString());
                lstProductProfile = this.DbContext.Database.SqlQuery<ProductProfile>(sql).ToList();
            }
            catch (Exception ex)
            {
                logger.Error("\t\tError DataAccess: Product; Function: GetProductInfo");
            }

            logger.Info("\t\tEnd DataAccess: Product; Function: GetProductInfo");
            return lstProductProfile;
        }

        public int CountProductInfo(string groupId, string subGroupId)
        {
            // ログを記述する
            logger.Info("\t\tStart DataAccess: Product; Function:CountProductInfo");
            int productCount = 0;
            string sql = string.Empty;
            string filter = string.Empty;

            try
            {
                filter += groupId != string.Empty ? " AND GroupId = {0}" : string.Empty;
                filter += subGroupId != string.Empty ? " AND SubGroupId = {1}" : string.Empty;
                filter = string.Format(filter, groupId, subGroupId);

                sql = string.Format(
                            @"SELECT Count(id) 
                            FROM Product
                            WHERE del_flg = 0
                            {0}", filter);
                productCount = this.DbContext.Database.SqlQuery<int>(sql).First();
            }
            catch (Exception ex)
            {
                logger.Error("\t\tError DataAccess: Product; Function: CountProductInfo");
                throw ex;
            }

            logger.Info("\t\tEnd DataAccess: Product; Function: CountProductInfo");
            return productCount;
        }

        public string GetNameInfo(string groupId, string subGroupId)
        {
            // ログを記述する
            logger.Info("\t\tStart DataAccess: Product; Function:GetNameInfo");
            string productCount = string.Empty;
            string sql = string.Empty;
            string filter = string.Empty;
            string join = string.Empty;
            string condition = string.Empty;
            string select = string.Empty;

            try
            {
                filter += groupId == string.Empty ? string.Empty : "AND ItemGroup.id = {0}";

                if (subGroupId != string.Empty)
                {
                    filter += " AND SubItemGroup.id = {1}";
                    join += @" INNER JOIN SubItemGroup
                                ON ItemGroup.id = SubItemGroup.GroupId";
                    condition += @" AND SubItemGroup.del_flg = 0 ";
                    select += @" + SubGroupName";
                }

                filter = string.Format(filter, groupId, subGroupId);


                sql = string.Format(
                            @"SELECT GroupName + '{0}' {1}
                            FROM ItemGroup
                                {2}
                            WHERE ItemGroup.del_flg = 0
                                {3}
                                {4}", Constants.SPECIAL_SPACE_CHAR, select,
                                    join, condition, filter);
                productCount = this.DbContext.Database.SqlQuery<string>(sql).FirstOrDefault();
            }
            catch (Exception ex)
            {
                logger.Error("\t\tError DataAccess: Product; Function: GetNameInfo");
                throw ex;
            }

            logger.Info("\t\tEnd DataAccess: Product; Function: GetNameInfo");
            return productCount;
        }

        public Product GetProductInfo(string groupId, string subGroupId, string productId)
        {
            // ログを記述する
            logger.Info("\t\tStart DataAccess: Product; Function:GetProductInfo");
            string sql = string.Empty;
            Product productInfo = new Product();

            try
            {
                sql = string.Format(@"
                                SELECT i.id AS ImageId, p.id, p.GroupId, p.SubGroupId,
                                    p.Name, p.Price, p.Description,
                                    p.Qty, p.Materials, p.GemType,
                                    p.Weight, p.Dimensions, p.FreeService, p.FeeService,
                                    p.MoreDescription, i.ImageName, p.ViewCount
                                FROM Product p
                                INNER JOIN Image i
                                    ON p.id = i.ProductId
                                    AND p.GroupId = i.GroupId
                                    AND p.SubGroupId = i.SubGroupId
                                    AND i.id = (
                                        SELECT MIN(id)
                                        FROM Image
                                        WHERE GroupId = p.GroupId
                                            AND SubGroupId = p.SubGroupId
				                            AND ProductId = p.id
                                    )
                                WHERE p.id = {0}
                                    AND p.GroupId = {1}
                                    AND p.SubGroupId = {2}
                                ORDER BY i.id ASC"
                                , productId, groupId, subGroupId);
                productInfo = this.DbContext.Database.SqlQuery<Product>(sql).FirstOrDefault();
            }
            catch (Exception ex)
            {
                logger.Error("\t\tError DataAccess: Product; Function: GetProductInfo");
                throw ex;
            }

            logger.Info("\t\tEnd DataAccess: Product; Function: GetProductInfo");
            return productInfo;
        }

        public int UpdateViewCount(string groupId, string subGroupId, string productId)
        {
            // ログを記述する
            logger.Info("\t\tStart DataAccess: Product; Function:UpdateViewCount");
            string sql = string.Empty;
            int result = 0;

            try
            {
                sql = string.Format(@"
                                UPDATE Product
                                SET ViewCount = ViewCount + 1
                                WHERE id = {0}
                                    AND GroupId = {1}
                                    AND SubGroupId = {2}"
                                , productId, groupId, subGroupId);
                result = this.DbContext.Database.ExecuteSqlCommand(sql);
            }
            catch (Exception ex)
            {
                logger.Error("\t\tError DataAccess: Product; Function: UpdateViewCount");
                throw ex;
            }

            logger.Info("\t\tEnd DataAccess: Product; Function: UpdateViewCount");
            return result;
        }

        public int RegisterProduct(string groupId, string subGroupId, UploadedProduct product, string date)
        {
            // ログを記述する
            logger.Info("\t\tStart DataAccess: Product; Function:RegisterProducts");
            string sql = string.Empty;
            int result = 0;

            try
            {
                ArrayList fileNameList = (ArrayList)Utils.GetSession(Constants.SESSION_UPLOADED_FILENAME);

                Product item = product.product;

                sql = string.Format(@"
                            DECLARE @i int
                            DECLARE @productName nvarchar(50)
                            DECLARE @productId int

                            SELECT @productId = ISNULL(MAX(id) + 1, 1)
                            FROM PRODUCT
                            
                            SET @i = 1
                            SET @productName = '{10}'
                            WHILE (@i <= 5 - LEN(@productId))
                            BEGIN
	                            SET @productName = @productName + '0'
	                            SET @i = @i + 1
                            END

                            SET @productName = @productName + CONVERT(varchar(50), @productId)

                            INSERT INTO Product (GroupId, SubGroupId, [Name], Price, Qty, 
                                Description, Materials, FreeService, FeeService, ViewCount, del_flg, UpdateDate) 
                            VALUES ({0}, {1}, @productName, {3}, {4}, N'{5}', '{6}', N'{7}', N'{8}', 0, 0, '{9}')
                            
                            SELECT @productId
                            SELECT @productName"
                        , groupId, subGroupId, string.Empty, item.Price, item.Qty, item.Description,
                        item.Materials, item.FreeService, item.FeeService, date, Constants.PRESET_PRODUCT_NAME);

                result = this.DbContext.Database.SqlQuery<int>(sql).FirstOrDefault();
            }
            catch (Exception ex)
            {
                logger.Error("\t\tError DataAccess: Product; Function: RegisterProducts");
                throw ex;
            }

            logger.Info("\t\tEnd DataAccess: Product; Function: RegisterProducts");
            return result;
        }

        public int DeleteProduct(string groupId, string subGroupId, string productId, string updateTime)
        {
            // ログを記述する
            logger.Info("\t\tStart DataAccess: Product; Function:DeleteProduct");
            string sql = string.Empty;
            int result = 0;

            try
            {
                sql = string.Format(@"
                                UPDATE Product
                                SET del_flg = 1,
                                    UpdateDate = '{3}'
                                WHERE id = {0}
                                    AND GroupId = {1}
                                    AND SubGroupId = {2}"
                                , productId, groupId, subGroupId, updateTime);
                result = this.DbContext.Database.ExecuteSqlCommand(sql);
            }
            catch (Exception ex)
            {
                logger.Error("\t\tError DataAccess: Product; Function: DeleteProduct");
                throw ex;
            }

            logger.Info("\t\tEnd DataAccess: Product; Function: DeleteProduct");
            return result;
        }

        public int DeleteProductsById(List<string> lstId, string updateTime)
        {
            // ログを記述する
            logger.Info("\t\tStart DataAccess: Product; Function:DeleteProduct");
            string sql = string.Empty;
            int result = 0;
            string condition = string.Empty;

            try
            {
                foreach (string id in lstId)
                {
                    condition += id + ",";
                }

                if (condition != string.Empty)
                {
                    condition = condition.Remove(condition.LastIndexOf(","));
                }

                sql = string.Format(@"
                                UPDATE Product
                                SET del_flg = 1,
                                    UpdateDate = '{1}'
                                WHERE id IN ({0})"
                                , condition, updateTime);
                result = this.DbContext.Database.ExecuteSqlCommand(sql);
            }
            catch (Exception ex)
            {
                logger.Error("\t\tError DataAccess: Product; Function: DeleteProduct");
                throw ex;
            }

            logger.Info("\t\tEnd DataAccess: Product; Function: DeleteProduct");
            return result;
        }

        public int DeleteProductsBySubGroupId(List<string> lstId, string updateTime)
        {
            // ログを記述する
            logger.Info("\t\tStart DataAccess: Product; Function:DeleteProductsBySubGroupId");
            string sql = string.Empty;
            int result = 0;
            string condition = string.Empty;

            try
            {
                foreach (string id in lstId)
                {
                    condition += id + ",";
                }

                if (condition != string.Empty)
                {
                    condition = condition.Remove(condition.LastIndexOf(","));
                }

                sql = string.Format(@"
                                UPDATE Product
                                SET del_flg = 1,
                                    UpdateDate = '{1}'
                                WHERE SubGroupId IN ({0})"
                                , condition, updateTime);
                result = this.DbContext.Database.ExecuteSqlCommand(sql);
            }
            catch (Exception ex)
            {
                logger.Error("\t\tError DataAccess: Product; Function: DeleteProductsBySubGroupId");
                throw ex;
            }

            logger.Info("\t\tEnd DataAccess: Product; Function: DeleteProductsBySubGroupId");
            return result;
        }

        public int UpdateProduct(Product p, string updateTime)
        {
            // ログを記述する
            logger.Info("\t\tStart DataAccess: Product; Function: UpdateProduct");
            string sql = string.Empty;
            int result = 0;

            try
            {
                sql = string.Format(@"
                                UPDATE Product
                                SET Price = {3},
                                    Qty = {4},
                                    Description = N'{5}',
                                    FreeService = N'{6}',
                                    FeeService = N'{7}',
                                    del_flg = 0,
                                    UpdateDate = '{8}'
                                WHERE id = {0}
                                    AND GroupId = {1}
                                    AND SubGroupId = {2}", 
                        p.id, p.GroupId, p.SubGroupId, p.Price, p.Qty, 
                        p.Description, p.FreeService, p.FeeService, updateTime);
                result = this.DbContext.Database.ExecuteSqlCommand(sql);
            }
            catch (Exception ex)
            {
                logger.Error("\t\tError DataAccess: Product; Function: UpdateProduct");
                throw ex;
            }

            logger.Info("\t\tEnd DataAccess: Product; Function: UpdateProduct");
            return result;
        }
    }
}